﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    /// <summary>
    /// 学员数据访问
    /// </summary>
    public class StudentService
    {
        #region 添加学员
        /// <summary>
        /// 判断学员身份证是否以及存在
        /// </summary>
        /// <param name="studentIdNo"></param>
        /// <returns></returns>
        public bool IsExistsStudentIdNo(string studentIdNo)
        {
            string sql = $"select Count(1) from Students where StudentIdNo={studentIdNo}";
            var result = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
            if (result == 1) return true;
            else return false;

        }

        /// <summary>
        /// 添加学员
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        public int AddStudent(Students student)
        {
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@StudentName",student.StudentName),
                new SqlParameter("@Gender",student.Gender),
                new SqlParameter("@Birthday",student.Birthday),
                new SqlParameter("@StudentIdNo",student.StudentIdNo),
                new SqlParameter("@PhoneNumber",student.PhoneNumber),
                new SqlParameter("@StudentAddress",student.StudentAddress),
                new SqlParameter("@ClassId",student.ClassId),
            };
            try
            {
                return Convert.ToInt32( SqlHelper.ExecuteScalar("usp_AddStudent",sqlParameters,true));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 查询学员

        /// <summary>
        /// 根基学员id查询学员信息
        /// </summary>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public StudentExt QueryStudent(string studentId)
        {
            string sql = "select StudentId,StudentName,Gender,Birthday,StudentIdNo,PhoneNumber,StudentAddress,b.ClassId,ClassName from";
            sql += " Students a inner join StudentClass b on a.ClassId=b.ClassId";
            sql += $" where StudentId={studentId}";
            StudentExt studentExt = null;
            SqlDataReader reader = SqlHelper.ExecuteReader(sql);
            if (reader.Read())
            {
                studentExt = new StudentExt
                {
                    StudentId = Convert.ToInt32(reader["StudentId"]),
                    StudentName = reader["StudentName"].ToString(),
                    Gender = reader["Gender"].ToString(),
                    Birthday = Convert.ToDateTime(reader["Birthday"]),
                    StudentIdNo = reader["StudentIdNo"].ToString(),
                    PhoneNumber = reader["PhoneNumber"].ToString(),
                    StudentAddress = reader["StudentAddress"].ToString(),
                    ClassId = Convert.ToInt32(reader["ClassId"]),
                    ClassName = reader["ClassName"].ToString(),
                };
            }
            reader.Close();
            return studentExt;
        }

        /// <summary>
        /// 修改时确认ID是否被应用
        /// </summary>
        /// <param name="studentIdNo"></param>
        /// <returns></returns>
        public bool IsExistsStudentIdNo(string studentIdNo,string studentId)
        {
            string sql = $"select Count(1) from Students where StudentIdNo={studentIdNo} and StudentId <>{studentId}";
            var result = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
            if (result == 1) return true;
            else return false;
        }




        /// <summary>
        /// 根基班级名称查询学员列表
        /// </summary>
        /// <param name="className"></param>
        /// <returns></returns>
        public List<StudentExt> GetStudent(string className)
        {
            string sql = "select StudentId,StudentName,Gender,Birthday,StudentAddress,b.ClassId,ClassName from";
            sql += " Students a inner join StudentClass b on a.ClassId=b.ClassId";
            sql += " where ClassName=@ClassName";
            SqlParameter[] sqlParameters = new SqlParameter[]
                {
                    new SqlParameter("@ClassName",className)
                };
            List<StudentExt> studentsList = new List<StudentExt>();
            SqlDataReader reader = SqlHelper.ExecuteReader(sql,sqlParameters);
            while (reader.Read())
            {
                studentsList.Add (new StudentExt
                {
                    StudentId = Convert.ToInt32(reader["StudentId"]),
                    StudentName = reader["StudentName"].ToString(),
                    Gender = reader["Gender"].ToString(),
                    Birthday = Convert.ToDateTime(reader["Birthday"]),
                    StudentAddress = reader["StudentAddress"].ToString(),       
                    ClassId = Convert.ToInt32(reader["ClassId"]),
                    ClassName = reader["ClassName"].ToString(),
                });
            }
            reader.Close();
            return studentsList;
        }
        #endregion

        #region 修改学员对象
        /// <summary>
        /// 修改学员信息
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        public int UpdateStudentInfoudentId(Students student)
        {
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@StudentName",student.StudentName),
                new SqlParameter("@Gender",student.Gender),
                new SqlParameter("@Birthday",student.Birthday),
                new SqlParameter("@StudentIdNo",student.StudentIdNo),
                new SqlParameter("@PhoneNumber",student.PhoneNumber),
                new SqlParameter("@StudentAddress",student.StudentAddress),
                new SqlParameter("@ClassId",student.ClassId),
                new SqlParameter("@StudentId",student.StudentId)
            };
            try
            {
                return Convert.ToInt32(SqlHelper.ExecuteNonQuery("usp_UpdateStudent", sqlParameters, true));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 删除学员
        /// <summary>
        /// 删除学员
        /// </summary>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public int DeleteStudentByStudentId(string studentId)
        {
            string sql = "delete from Students where StudentId=@StudentId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@StudentId",studentId),
               
            };
            try
            {
                return Convert.ToInt32(SqlHelper.ExecuteNonQuery(sql, sqlParameters, false));
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547)
                {
                    throw new Exception($"学号【{studentId}】以及被其他学员数据表引用，不能直接删除！");
                }
                else
                {
                    throw new Exception("数据库操作异常："+ex.Message);
                }  
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
